import pandas as pd
import numpy as np
import glob

# 写成函数
def deal_data(path_file, sn):
    path_name = path_file.split("\\")[-1]
    print(f"处理：{path_name}, sheet:{sn}")
    df1 = pd.read_excel(path_file, sheet_name=sn, skiprows=1)
    df1_name = df1.columns.tolist()
    all_ = np.sum(df1[df1_name[1]].notnull())
    ok_ , counts = [0]*4, 0
    for i in range(4):
        ok_[i] = np.sum(df1[df1_name[3+i]].notnull())
        counts += ok_[i]
    ng = np.sum(df1[df1_name[7]].notnull())
    buliang = np.sum(df1[df1_name[8]].notnull())
    bool_ = all_ == counts + ng
    print(f" \
       没有踢除'不良明细'时的数据统计：\n \
       总数: {all_} \n\
       {df1_name[3]}: {ok_[0]}  \n \
       {df1_name[4]}: {ok_[1]}  \n\
       {df1_name[5]}: {ok_[2]}  \n \
       {df1_name[6]}: {ok_[3]}   \n \
       {df1_name[7]}: {ng}  \n \
       {df1_name[8]}: {buliang} \n \
       数据是否正确： {bool_} \n \
     ")
    
    del_, okk_, counts = [0]*4, [0]*4, 0
    for i in range(4):
        del_[i] = np.sum(df1[df1_name[3+i]].notnull() & df1[df1_name[8]].notnull())
        okk_[i] = ok_[i] - del_[i]
        counts += okk_[i]
    all_2 = counts + ng
    print(f" \
       踢除'不良明细'时的数据统计：\n \
       总数：all_2 \n \
       {df1_name[3]}: {okk_[0]}  \n \
       {df1_name[4]}: {okk_[1]} \n\
       {df1_name[5]}: {okk_[2]} \n \
       {df1_name[6]}: {okk_[3]}  \n \
     ")
          
    name = [df1_name[3], df1_name[4], df1_name[5], df1_name[6], df1_name[7], df1_name[8], "总数","数据是否正确"]
    col1 = [ok_[0], ok_[1], ok_[2], ok_[3], ng, buliang,all_, bool_]
    col2 = [okk_[0], okk_[1], okk_[2], okk_[3], ng, buliang, all_2, bool_]
    df2 = pd.DataFrame({"统计类别":name,"没有踢除 不良明细":col1, "踢除 不良明细":col2})
    path_name.split("xlsx")
    df2.to_csv(f"./result/{sn}_{path_name[:-5]}.csv")

def main():
    paths = glob.glob("./data/*xlsx")
    for p in paths:
        sheet = pd.read_excel(p, sheet_name=None)
        sheet_names = list(sheet.keys())
        for sheet_name in sheet_names:
            deal_data(p, sheet_name)    
if __name__ == main():
    main()
